package niord.apps.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class DataBaseAdapter {

	// Tables
	private static final String PRODUCTS_TABLE = "products";
	private static final String PERSONS_TABLE = "persons";
	private static final String ORDERS_TABLE = "orders";
	private static final String PLACES_TABLE = "places";

	// Table fields
	public static final String KEY_ID = "id";

	public static final String KEY_NAME = "name";
	public static final String KEY_PRICE = "price";	
	public static final String KEY_QUAN = "quantity";
	public static final String KEY_ADDR = "address";
	public static final String KEY_PHN = "phone";

	public static final String KEY_TYPE_ID = "type_id";
	public static final String KEY_UOM_ID = "uom_id";
	public static final String KEY_PSN_ID = "person_id";
	public static final String KEY_PRD_ID = "product_id";
	public static final String KEY_PLC_ID = "place_id";

	private Context context;
	private SQLiteDatabase db;
	private DataBaseHelper dbHelper;

	public DataBaseAdapter(Context context) {
		this.context = context;
	}

	public DataBaseAdapter open() throws SQLException {
		dbHelper = new DataBaseHelper(context);
		db = dbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		dbHelper.close();
	}

	/***************************************************************************/
	/** Create entries *********************************************************/
	/***************************************************************************/
	public int createProduct(String name,
										Double price,
										int type_id,
										int uom_id,
										int place_id) {
		ContentValues values = new ContentValues();
		values.put(KEY_NAME, name);
		values.put(KEY_PRICE, price);
		values.put(KEY_TYPE_ID, type_id);
		values.put(KEY_UOM_ID, uom_id);
		values.put(KEY_PLC_ID, place_id);

		return (int)db.insert(PRODUCTS_TABLE, null, values);
	}

	public int createPerson(String name) {
		ContentValues values = new ContentValues();
		values.put(KEY_NAME, name);

		return (int)db.insert(PERSONS_TABLE, null, values);
	}

	public int createOrder(int person_id, int product_id, int quantity) {
		ContentValues values = new ContentValues();
		values.put(KEY_PSN_ID, person_id);
		values.put(KEY_PRD_ID, product_id);
		values.put(KEY_QUAN, quantity);

		return (int)db.insert(ORDERS_TABLE, null, values);
	}
	
	public int createPlace(String name, String address, String phone) {
		ContentValues values = new ContentValues();
		values.put(KEY_NAME, name);
		values.put(KEY_ADDR, address);
		values.put(KEY_PHN, phone);

		return (int)db.insert(PLACES_TABLE, null, values);
	}
	/***************************************************************************/
	/** Update entries *********************************************************/
	/***************************************************************************/
	public boolean updateProduct(	int id,
											String name,
											Double price,
											int type_id,
											int uom_id,
											int place_id) {
			ContentValues values = new ContentValues();
			values.put(KEY_NAME, name);
			values.put(KEY_PRICE, price);
			values.put(KEY_TYPE_ID, type_id);
			values.put(KEY_UOM_ID, uom_id);
			values.put(KEY_PLC_ID, place_id);

		return db.update(PRODUCTS_TABLE, values, KEY_ID + "=" + id, null) > 0;
	}

	public boolean updatePerson(int id, String name) {
		ContentValues values = new ContentValues();
		values.put(KEY_NAME, name);

		return db.update(PERSONS_TABLE, values, KEY_ID + "=" + id, null) > 0;
	}

	public boolean updateOrder(	int id,
											int person_id,
											int product_id,
											int quantity) {
		ContentValues values = new ContentValues();
		values.put(KEY_PSN_ID, person_id);
		values.put(KEY_PRD_ID, product_id);
		values.put(KEY_QUAN, quantity);

		return db.update(ORDERS_TABLE, values, KEY_ID + "=" + id, null) > 0;
	}
	
	public boolean updatePlace(int id, String name) {
		ContentValues values = new ContentValues();
		values.put(KEY_NAME, name);

		return db.update(PLACES_TABLE, values, KEY_ID + "=" + id, null) > 0;
	}
	/***************************************************************************/
	/** Delete entries *********************************************************/
	/***************************************************************************/
	public boolean deleteAllProducts() {
		return db.delete(PRODUCTS_TABLE, "1", null) > 0;
	}

	public boolean deleteAllPersons() {
		return db.delete(PERSONS_TABLE, "1", null) > 0;
	}

	public boolean deleteAllOrders() {
		return db.delete(ORDERS_TABLE, "1", null) > 0;
	}
	
	public boolean deleteAllPlaces() {
		return db.delete(PLACES_TABLE, "1", null) > 0;
	}

	public void deleteAll() {
		deleteAllProducts();
		deleteAllPersons();
		deleteAllOrders();
		deleteAllPlaces();
	}
	/***************************************************************************/
	/** Fetch all entries ******************************************************/
	/***************************************************************************/
	public Cursor fetchAllProducts() {
		Cursor mCursor = db.query(PRODUCTS_TABLE, 
				new String[] {KEY_ID, KEY_NAME, KEY_PRICE, KEY_TYPE_ID, KEY_UOM_ID, KEY_PLC_ID}, null, null, null,
				null, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public Cursor fetchAllPersons() {
		Cursor mCursor = db.query(PERSONS_TABLE,
				new String[] { KEY_ID, KEY_NAME }, null, null, null, null, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public Cursor fetchAllOrders() {
		Cursor mCursor = db.query(ORDERS_TABLE, 
				new String[] { KEY_ID, KEY_PSN_ID, KEY_PRD_ID, KEY_QUAN }, null, null, null, null, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	public Cursor fetchAllPlaces() {
		Cursor mCursor = db.query(ORDERS_TABLE, 
				new String[] { KEY_ID, KEY_NAME, KEY_ADDR, KEY_PHN }, null, null, null, null, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	/***************************************************************************/
	/** Fetch entries by id ****************************************************/
	/***************************************************************************/
	public Cursor fetchProductById(int id) throws SQLException {
		Cursor mCursor = db.query(PRODUCTS_TABLE, new String[] { KEY_ID,
				KEY_NAME, KEY_PRICE, KEY_TYPE_ID, KEY_UOM_ID, KEY_PLC_ID}, KEY_ID + "=" + id,
				null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public Cursor fetchPersonsById(int id) throws SQLException {
		Cursor mCursor = db.query(PERSONS_TABLE,
				new String[] { KEY_ID, KEY_NAME }, KEY_ID + "=" + id, null, null,
				null, null, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public Cursor fetchOrdersById(int id) throws SQLException {
		Cursor mCursor = db.query(ORDERS_TABLE, new String[] { KEY_ID,
				KEY_PSN_ID, KEY_PRD_ID, KEY_QUAN }, KEY_ID + "=" + id, null, null,
				null, null, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	public Cursor fetchPlacesById(int id) throws SQLException {
		Cursor mCursor = db.query(ORDERS_TABLE, new String[] { KEY_ID,
				KEY_NAME, KEY_ADDR, KEY_PHN }, KEY_ID + "=" + id, null, null,
				null, null, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	/***************************************************************************/
	/** Fetch entries by parameter *********************************************/
	/***************************************************************************/
	public Cursor fetchProductByPlace(int place_id) throws SQLException {
		Cursor mCursor = db.query(PRODUCTS_TABLE, new String[] { KEY_ID,
				KEY_NAME, KEY_PRICE, KEY_TYPE_ID, KEY_UOM_ID, KEY_PLC_ID}, KEY_PLC_ID + "=" + place_id,
				null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	public Cursor fetchOrdersByPerson(int person_id) throws SQLException {
		Cursor mCursor = db.query(ORDERS_TABLE, new String[] { KEY_ID,
				KEY_PSN_ID, KEY_PRD_ID, KEY_QUAN }, KEY_PSN_ID + "=" + person_id, null, null,
				null, null, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
}